const mysql = require('mysql2/promise')
const config = require('./index')

// 创建数据库连接池
const pool = mysql.createPool({
  host: config.database.host,
  port: config.database.port,
  user: config.database.user,
  password: config.database.password,
  database: config.database.name,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  acquireTimeout: 10000, // 减少到10秒
  timeout: 10000, // 减少到10秒
  charset: 'utf8mb4'
})

// 测试数据库连接
async function testConnection () {
  try {
    const connection = await pool.getConnection()
    console.log('✅ 数据库连接池创建成功')
    connection.release()
    return true
  } catch (error) {
    console.error('❌ 数据库连接失败:', error.message)
    return false
  }
}

// 执行查询
const query = async (sql, params = []) => {
  try {
    const [rows, fields] = await pool.execute(sql, params)
    return rows
  } catch (error) {
    console.error('数据库查询错误:', error)
    throw error
  }
}

// 执行查询并返回完整结果（用于INSERT等需要metadata的操作）
const execute = async (sql, params = []) => {
  try {
    return await pool.execute(sql, params)
  } catch (error) {
    console.error('数据库执行错误:', error)
    throw error
  }
}

// 执行DDL语句（触发器、存储过程等，不支持prepared statement）
const executeDDL = async sql => {
  try {
    const [rows, fields] = await pool.query(sql)
    return rows
  } catch (error) {
    console.error('数据库DDL执行错误:', error)
    throw error
  }
}

// 执行事务
const transaction = async callback => {
  const connection = await pool.getConnection()
  await connection.beginTransaction()

  try {
    const result = await callback(connection)
    await connection.commit()
    return result
  } catch (error) {
    await connection.rollback()
    throw error
  } finally {
    connection.release()
  }
}

// 初始化数据库表
const initDatabase = async () => {
  try {
    console.log('🚀 开始初始化数据库...')

    // 用户表
    await query(`
      CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        email VARCHAR(255) UNIQUE NOT NULL,
        username VARCHAR(100) NOT NULL,
        password_hash VARCHAR(255) NOT NULL,
        nickname VARCHAR(100) DEFAULT NULL,
        avatar_url VARCHAR(500) DEFAULT NULL,
        role ENUM('user', 'admin', 'moderator') DEFAULT 'user',
        is_vip BOOLEAN DEFAULT FALSE,
        vip_expire_date DATETIME DEFAULT NULL,
        coins INT DEFAULT 0,
        reading_time INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_email (email),
        INDEX idx_username (username),
        INDEX idx_role (role)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    // 小说表
    await query(`
      CREATE TABLE IF NOT EXISTS novels (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        author VARCHAR(100) NOT NULL,
        description TEXT,
        cover_url VARCHAR(500),
        category VARCHAR(50),
        tags JSON,
        status ENUM('serializing', 'completed', 'paused') DEFAULT 'serializing',
        word_count INT DEFAULT 0,
        chapter_count INT DEFAULT 0,
        rating DECIMAL(3,2) DEFAULT 0.00,
        view_count INT DEFAULT 0,
        like_count INT DEFAULT 0,
        is_vip BOOLEAN DEFAULT FALSE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_category (category),
        INDEX idx_status (status),
        INDEX idx_rating (rating),
        INDEX idx_view_count (view_count),
        FULLTEXT idx_title_author (title, author)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    // 章节表
    await query(`
      CREATE TABLE IF NOT EXISTS chapters (
        id INT AUTO_INCREMENT PRIMARY KEY,
        novel_id INT NOT NULL,
        title VARCHAR(255) NOT NULL,
        content LONGTEXT NOT NULL,
        chapter_number INT NOT NULL,
        word_count INT DEFAULT 0,
        is_vip BOOLEAN DEFAULT FALSE,
        price INT DEFAULT 0,
        view_count INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (novel_id) REFERENCES novels(id) ON DELETE CASCADE,
        INDEX idx_novel_id (novel_id),
        INDEX idx_chapter_number (chapter_number),
        UNIQUE KEY unique_novel_chapter (novel_id, chapter_number)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    // 用户书架表
    await query(`
      CREATE TABLE IF NOT EXISTS user_bookshelf (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        novel_id INT NOT NULL,
        last_read_chapter_id INT DEFAULT NULL,
        reading_progress DECIMAL(5,2) DEFAULT 0.00,
        is_favorite BOOLEAN DEFAULT FALSE,
        added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        last_read_at TIMESTAMP NULL,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (novel_id) REFERENCES novels(id) ON DELETE CASCADE,
        FOREIGN KEY (last_read_chapter_id) REFERENCES chapters(id) ON DELETE SET NULL,
        UNIQUE KEY unique_user_novel (user_id, novel_id),
        INDEX idx_user_id (user_id),
        INDEX idx_novel_id (novel_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    // 阅读记录表
    await query(`
      CREATE TABLE IF NOT EXISTS reading_history (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        novel_id INT NOT NULL,
        chapter_id INT NOT NULL,
        reading_time INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (novel_id) REFERENCES novels(id) ON DELETE CASCADE,
        FOREIGN KEY (chapter_id) REFERENCES chapters(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id),
        INDEX idx_novel_id (novel_id),
        INDEX idx_created_at (created_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    // 支付订单表
    await query(`
      CREATE TABLE IF NOT EXISTS payment_orders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        order_no VARCHAR(64) UNIQUE NOT NULL,
        type ENUM('vip', 'coins', 'chapter') NOT NULL,
        amount DECIMAL(10,2) NOT NULL,
        status ENUM('pending', 'paid', 'failed', 'refunded') DEFAULT 'pending',
        payment_method VARCHAR(50),
        third_party_order_id VARCHAR(255),
        metadata JSON,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id),
        INDEX idx_order_no (order_no),
        INDEX idx_status (status)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    // 用户会话表
    await query(`
      CREATE TABLE IF NOT EXISTS user_sessions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        token_hash VARCHAR(255) NOT NULL,
        device_info VARCHAR(500),
        ip_address VARCHAR(45),
        expires_at DATETIME NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id),
        INDEX idx_token_hash (token_hash),
        INDEX idx_expires_at (expires_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `)

    console.log('✅ 数据库表初始化完成')

    // 插入默认数据
    await initDefaultData()
  } catch (error) {
    console.error('❌ 数据库初始化失败:', error)
    throw error
  }
}

// 插入默认数据
const initDefaultData = async () => {
  try {
    // 检查是否已有数据
    const userCount = await query('SELECT COUNT(*) as count FROM users')
    if (userCount[0].count > 0) {
      console.log('📚 数据库已有数据，跳过默认数据插入')
      return
    }

    console.log('📝 插入默认数据...')

    // 创建默认管理员用户
    const bcrypt = require('bcryptjs')
    const adminPassword = await bcrypt.hash('admin123', 12)

    await query(
      `
      INSERT INTO users (email, username, password_hash, role, is_vip, coins) 
      VALUES (?, ?, ?, ?, ?, ?)
    `,
      ['admin@novel.com', '管理员', adminPassword, 'admin', true, 1000]
    )

    // 插入示例小说数据
    const novels = [
      {
        title: '斗罗大陆',
        author: '唐家三少',
        description:
          '唐门外门弟子唐三，因偷学内门绝学为唐门所不容，跳崖明志时却发现没有死，反而以另外一个身份来到了另一个世界，一个属于武魂的世界，名叫斗罗大陆。',
        category: '玄幻',
        tags: JSON.stringify(['玄幻', '武魂', '斗罗', '经典']),
        status: 'completed',
        word_count: 3000000,
        chapter_count: 336,
        rating: 9.5,
        view_count: 1000000,
        like_count: 50000
      },
      {
        title: '完美世界',
        author: '辰东',
        description:
          '一粒尘可填海，一根草斩尽日月星辰，弹指间天翻地覆。群雄并起，万族林立，诸圣争霸，乱天动地。',
        category: '玄幻',
        tags: JSON.stringify(['玄幻', '修仙', '热血', '经典']),
        status: 'completed',
        word_count: 5000000,
        chapter_count: 1677,
        rating: 9.3,
        view_count: 800000,
        like_count: 40000
      },
      {
        title: '遮天',
        author: '辰东',
        description:
          '冰冷与黑暗并存的宇宙深处，九具庞大的龙尸拉着一口青铜古棺，亘古长存。',
        category: '玄幻',
        tags: JSON.stringify(['玄幻', '修仙', '古风', '经典']),
        status: 'completed',
        word_count: 6000000,
        chapter_count: 1878,
        rating: 9.4,
        view_count: 900000,
        like_count: 45000
      }
    ]

    for (const novel of novels) {
      const result = await query(
        `
        INSERT INTO novels (title, author, description, category, tags, status, word_count, chapter_count, rating, view_count, like_count)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `,
        [
          novel.title,
          novel.author,
          novel.description,
          novel.category,
          novel.tags,
          novel.status,
          novel.word_count,
          novel.chapter_count,
          novel.rating,
          novel.view_count,
          novel.like_count
        ]
      )

      // 为每本小说插入一些示例章节
      const novelId = result.insertId
      for (let i = 1; i <= 5; i++) {
        await query(
          `
          INSERT INTO chapters (novel_id, title, content, chapter_number, word_count, is_vip, price)
          VALUES (?, ?, ?, ?, ?, ?, ?)
        `,
          [
            novelId,
            `第${i}章 ${novel.title}的开始`,
            `这是《${
              novel.title
            }》第${i}章的内容。\n\n在这一章中，故事开始展开，主角踏上了他的冒险之旅...\n\n${'章节内容'.repeat(
              100
            )}`,
            i,
            Math.floor(Math.random() * 3000) + 1000,
            i > 3, // 前3章免费，后面章节VIP
            i > 3 ? 5 : 0
          ]
        )
      }
    }

    console.log('✅ 默认数据插入完成')
  } catch (error) {
    console.error('❌ 默认数据插入失败:', error)
    throw error
  }
}

module.exports = {
  pool,
  query,
  execute,
  executeDDL,
  transaction,
  testConnection,
  initDatabase
}
